﻿Imports System.Data
Imports Sybase.Data.AseClient

Imports System.IO
Imports System.Xml

Imports System.Collections.ObjectModel

Public Class CFLServiciosMacrosSyBaseDB

    Public Shared Function ObtieneSaldosFondos(ByRef lstFondos As List(Of CTSaldoFondosPS), ByVal strFecha As String) As DataTable

        Dim strFondoSalDiso As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtFondoSalDiso As New DataTable()

        'Dim idRegBit As Integer


        strFondoSalDiso = " select RTRIM(p.PF_COD) as fondo, ROUND(p.AMT_CURRENT,2) as saldo " & _
                          " from ID_POS_CASH p noholdlock, PORTFOLIO f noholdlock " & _
                          " where  f.PF_COD in(" & GenerarCondicionFondos(lstFondos) & ") and f.PF_COD = p.PF_COD " & _
                          " and p.SETTLE_DAT  =  CONVERT (DATE,'" & strFecha & "')" & _
                          " and p.CUR_COD ='MXN' " & _
                          " and (f.PF_C_FREE15  <> 'Y' or  f.PF_C_FREE15 is null) order by p.PF_COD"

        'idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "FondosSalLiquidezDB", "Consulta saldos: " & strFondoSalDiso)

        dtFondoSalDiso = dbCnn.EjecutarConsulta(strFondoSalDiso, pm, sMsgError)

        'CUtileria.CompletarRegBitacora(idRegBit)


        If Not sMsgError.Equals("") Then
            'idRegBit = CUtileria.GrabarBitacora("Liquidación-Porvenir-Saldos", "FondosSalLiquidezDB", sMsgError)
            'CUtileria.CompletarRegBitacora(idRegBit)
            Throw New Exception(sMsgError)
        End If

        Return dtFondoSalDiso

    End Function

    Private Shared Function GenerarCondicionFondos(ByRef lstFondos As  List(Of CTSaldoFondosPS)) As String

        Dim fondos As New System.Text.StringBuilder
        Try

            For Each fondo As CTSaldoFondosPS In lstFondos
                fondos.Append("'" & fondo.fondo.Trim & "',")
            Next

            fondos.Remove(fondos.Length - 1, 1)
            Dim aux As String = fondos.ToString()
            Return aux
        Catch ex As Exception
            Throw ex
        End Try


    End Function

    Public Shared Function ObtieneInstrumento(ByVal nomInstrumento As String) As DataTable

        Dim strInstrumento As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtInstrumento As New DataTable()

        strInstrumento = "SELECT RTRIM(LTRIM(INST_SH_NAM)) AS INST_SH_NAM, INST_NUM  FROM INSTRUMENT noholdlock WHERE STATUS <> 'F' AND RTRIM(LTRIM(INST_SH_NAM)) = '" & nomInstrumento & "'"

        dtInstrumento = dbCnn.EjecutarConsulta(strInstrumento, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtInstrumento

    End Function

    Public Shared Function ObtieneInstrumentosPosicion() As DataTable

        Dim strInstrumentos As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtInstrumentos As New DataTable()

        strInstrumentos = " select RTRIM(LTRIM(ins.INST_SH_NAM)) INSTRUMENTO, ins.INST_NUM INST_NUM " & _
                          " from ID_POS po noholdlock, INSTRUMENT ins noholdlock " & _
                          " where ins.INST_NUM = po.INST_NUM " & _
                          " and po.POS_TYP = 'NOR' " & _
                          " and po.POS_QTY <> 0 " & _
                          " and ins.STATUS <> 'F' " & _
                          " group by ins.INST_SH_NAM, ins.INST_NUM"


        dtInstrumentos = dbCnn.EjecutarConsulta(strInstrumentos, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtInstrumentos
    End Function

    Public Shared Function ObtieneInstrumentosPosicionHist(ByVal strFecha As String) As DataTable

        Dim strInstrumentos As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtInstrumentos As New DataTable()

        strInstrumentos = " select RTRIM(LTRIM(ins.INST_SH_NAM)) INSTRUMENTO" & _
                          " from ID_HISTO_POS po noholdlock, INSTRUMENT ins noholdlock" & _
                          " where ins.INST_NUM = po.INST_NUM " & _
                          " and po.POS_TYP = 'NOR' " & _
                          " and po.POS_QTY <> 0 " & _
                          " and po.POS_DAT = '" & strFecha & "'" & _
                          " and ins.STATUS <> 'F' " & _
                          " group by ins.INST_SH_NAM"

        dtInstrumentos = dbCnn.EjecutarConsulta(strInstrumentos, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtInstrumentos
    End Function

    Public Shared Function ObtieneInstrumentosEventos(ByVal strFecha As String) As DataTable

        Dim strInstrumentos As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtInstrumentos As New DataTable()

        strInstrumentos = " select ins.INST_NUM INST_NUM, RTRIM(LTRIM(ins.INST_SH_NAM)) INSTRUMENTO, RTRIM(LTRIM(ev.XACTC_TYP)) EVT_TYP" & _
                          " from ID_EVT ev noholdlock, INSTRUMENT ins noholdlock " & _
                          " where ins.INST_NUM = ev.INST_CONT_NUM " & _
                          " and ev.EVT_DAT = '" & strFecha & "'" & _
                          " and ins.STATUS <> 'F' " & _
                          " and RTRIM(LTRIM(ev.XACTC_TYP)) in ('COUPAY','BONDRED')" & _
                          " and ev.EVT_NAT <> 'D'"


        dtInstrumentos = dbCnn.EjecutarConsulta(strInstrumentos, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtInstrumentos
    End Function

    Public Shared Function ObtieneEventosDiv(ByVal strFecha As String, ByVal InstNum As Long) As DataTable

        Dim strInstrumentos As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtInstrumentos As New DataTable()

        strInstrumentos = " select top 1 ev.EVT_NUM, ev.EVT_DAT, RTRIM(LTRIM(ev.CUR_COD)) CUR_COD, UNIT_GROSS_AMT UNIT_GROSS_AMT" & _
                          " from ID_EVT ev noholdlock, INSTRUMENT ins noholdlock " & _
                          " where ins.INST_NUM = ev.INST_CONT_NUM " & _
                          " and ev.EVT_DAT >= '" & strFecha & "'" & _
                          " and RTRIM(LTRIM(ev.XACTC_TYP)) = 'DIVREC'" & _
                          " and ins.INST_NUM = " & InstNum & _
                          " and ins.STATUS <> 'F' " & _
                          " and ev.EVT_NAT <> 'D' " & _
                          " order by ev.EVT_DAT"


        dtInstrumentos = dbCnn.EjecutarConsulta(strInstrumentos, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtInstrumentos
    End Function

    Public Shared Function ObtieneInstrumentos(ByVal cadena As String) As DataTable

        Dim strInstrumentos As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtInstrumentos As New DataTable()


        strInstrumentos = "SELECT RTRIM(LTRIM(INST_SH_NAM)) INSTRUMENTO, INST_NUM, INST_C_FREE2  FROM INSTRUMENT noholdlock WHERE STATUS <> 'F' AND RTRIM(LTRIM(INST_SH_NAM)) in (" & cadena & ")"


        dtInstrumentos = dbCnn.EjecutarConsulta(strInstrumentos, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtInstrumentos
    End Function

    Public Shared Function ObtieneInstrumentosPorEmisora(ByVal cadena As String) As DataTable

        Dim strInstrumentos As String
        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim dtInstrumentos As New DataTable()


        strInstrumentos = "SELECT RTRIM(LTRIM(INST_SH_NAM)) INSTRUMENTO, INST_NUM  FROM INSTRUMENT noholdlock WHERE STATUS <> 'F' AND " _
            & " CASE WHEN charindex('_',INST_SH_NAM, charindex('_',INST_SH_NAM) + 1) > 0 AND charindex('_',INST_SH_NAM) > 0 THEN " _
            & " substring(INST_SH_NAM,charindex('_',INST_SH_NAM)+1, charindex('_',INST_SH_NAM, charindex('_',INST_SH_NAM) + 1) - charindex('_',INST_SH_NAM) - 1) " _
            & " ELSE INST_SH_NAM END in (" & cadena & ")" _
            & " AND substring(INST_SH_NAM, 1,1) = '1'"


        dtInstrumentos = dbCnn.EjecutarConsulta(strInstrumentos, pm, sMsgError)

        If Not sMsgError.Equals("") Then
            Throw New Exception(sMsgError)
        End If

        Return dtInstrumentos
    End Function

    Public Shared Function ObtieneFechasInhabiles(ByVal fechaInicio As Date, ByVal fechaFin As Date) As DataTable

        Dim sMsgError As String = ""
        Dim dbCnn As New CBaseDeDatosSyBase()
        Dim pm As New List(Of AseParameter)

        Dim db As New CBaseDeDatosSQLServer()
        Dim sSql As String

        Dim dtFechas As New DataTable()

        Try
            sSql = "select DAT_REF from ID_DAT noholdlock where COUNTRY = 'MEX' and DAT_OPEN_FLG = 'N' and DAT_REF between '" & fechaInicio.ToString("yyyy-MM-dd") & "' and '" & fechaFin.ToString("yyyy-MM-dd") & "'"
            
            dtFechas = dbCnn.EjecutarConsulta(sSql, pm, sMsgError)

            If Not sMsgError.Equals("") Then
                Throw New Exception(sMsgError)
            End If

            Return dtFechas

        Catch ex As Exception
    
            Throw ex
        End Try

    End Function

End Class
